Imports MyClasses
Imports System.Data.SqlClient
Imports System.Text
Imports System.Configuration

Public Class DPurchase
    Dim objLocPurchase As CPurchase
    Dim intPurchaseID As Integer = 0
    Dim SeqPurchaseID As String = 0
    Public Function Save(ByRef ObjPurchase As CPurchase, ByRef objPurchaseItem() As CNGItem, ByRef dtbOtherCharges As DataTable, ByVal dtbTaxes As DataTable, ByVal intVoucherID As Integer) As Integer
        Dim dbConnection As New dbAccess
        Dim intResult As Integer = 0

        Try
            objLocPurchase = ObjPurchase
            dbConnection.BeginTransaction()
            If intVoucherID > 0 Then
                intPurchaseID = intVoucherID
                UpdatePurchase(ObjPurchase, dbConnection, intVoucherID)
            Else

                intPurchaseID = SavePurchase(ObjPurchase, dbConnection)

            End If



            If intPurchaseID > 0 Then
                If ObjPurchase.PurchaseType <> DGeneral.DocTypes.PURCHASE_INVOICE_RETURN AndAlso intVoucherID > 0 Then
                    intResult = 1
                Else

                    If intVoucherID > 0 Then
                        If objLocPurchase.PurchaseType = DGeneral.DocTypes.PURCHASE_GRN Then
                            If UpdatePurchaseItemDetails(intVoucherID, dbConnection) = 0 Then
                                intResult = 0
                                Exit Function
                            End If
                        End If
                        '
                        intResult = dbConnection.ExecuteNonQuery("DELETE FROM tPurchaseDet WHERE PurchaseID=" & intPurchaseID.ToString())
                    End If

                    objLocPurchase.PurchaseBillID = intPurchaseID
                    intResult = SavePurchaseItemDetail(intPurchaseID, objPurchaseItem, dbConnection)
                    If objLocPurchase.PurchaseType = DGeneral.DocTypes.PURCHASE_INVOICE Then
                        If intResult > 0 Then
                            intResult = dbConnection.ExecuteNonQuery("UPDATE tPURCHASE SET BILLED=1 WHERE PurchaseID=" & ObjPurchase.PO_GRNID.ToString())
                        End If

                    End If
                End If
            End If
            If intResult > 0 AndAlso SaveOtherCharges(dtbOtherCharges, dbConnection) = 0 Then
                intResult = 0
                Exit Function
            End If

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "Save")
            intResult = 0
        Finally
            If intResult = 0 Then
                intPurchaseID = 0
                dbConnection.RollBackTransaction()
            Else
                dbConnection.CommitTransaction()
            End If
            dbConnection.closeSqlcon()
            If intPurchaseID = 0 Then
                Save = 0
            Else
                If intVoucherID > 0 Then
                    Save = intVoucherID
                Else
                    Save = SeqPurchaseID
                End If
            End If
            'Save = intPurchaseID
        End Try
    End Function

    Public Sub updateBilledStatus()
        DbHelper.ExecuteNonQuery("UPDATE tPurchase SET Billed=1 WHERE PurchaseID=" & intPurchaseID.ToString)
    End Sub
    Private Function funPriSaveTaxes(dtbTax As DataTable, ByRef dbConnection As dbAccess) As Int16
        dbConnection.ExecuteNonQuery("DELETE FROM tPurchaseTax WHERE PurchaseID=" & intPurchaseID.ToString())
        Dim param(4) As SqlParameter
        Dim strSql As String = "INSERT INTO tPurchaseTax(PurchaseID,TaxID,TAX,TaxableAmt,TaxAmt)" & _
                                              "VALUES(@PurchaseID,@TaxID,@TAX,@TaxableAmt,@TaxAmt)"
        Dim intResult As Integer
        For Each taxRow As DataRow In dtbTax.Rows
            If taxRow.Item(2) > 0 Then

                param(0) = New SqlParameter("@PurchaseID", intPurchaseID)
                param(1) = New SqlParameter("@TaxID", taxRow.Item(0))
                param(2) = New SqlParameter("@TAX", taxRow.Item(1))
                param(3) = New SqlParameter("@TaxableAmt", taxRow.Item(2))
                param(4) = New SqlParameter("@TaxAmt", taxRow.Item(3))
                intResult += dbConnection.ExecuteNonQuery(strSql, param)
            End If

        Next
        Return intResult
    End Function
    Public Function SavePurchase(ByRef ObjPurchase As CPurchase, ByRef dbConnection As dbAccess) As Integer

        Dim strSql As String = ""
        Dim param(21) As SqlParameter
        Try
            strSql = "INSERT INTO tPurchase(SeqPurchaseID,PartyCode,PO_GRNID,PurchaseDate,SupplierDC,PromoterID,SiteID,Amount,OtherCharges,TotalTaxes,Discount,GrossAmount,ShipingAddress,Status,CU,CD,LU,LD,FinYrID,PurchaseType,PriceListID,Billed)" & _
                     "VALUES(@SeqPurchaseID,@PartyCode,@PO_GRNID,@PurchaseDate,@SupplierDC,@PromoterID,@SiteID,@Amount,@OtherCharges,@TotalTaxes,@Discount,@GrossAmount,@ShipingAddress,@Status,@CU,@CD,@LU,@LD,@FinYrID,@PurchaseType,@PriceListID,@Billed)"
            ''''''''''''''''ShipingAddress,Status,CU,CD,LU,LD,FinYrID,PurchaseType

            SeqPurchaseID = DGeneral.GetSeqNum(IIf(ObjPurchase.PurchaseType = DGeneral.DocTypes.PURCHASE_INVOICE, DGeneral.DocTypes.PURCHASE_GRN, ObjPurchase.PurchaseType), dbConnection, True)
            '  SeqPurchaseID = DGeneral.GetSeqNum(ObjPurchase.PurchaseType, dbConnection, True)
            param(0) = New SqlParameter("@SeqPurchaseID", SeqPurchaseID)
            param(1) = New SqlParameter("@PartyCode", ObjPurchase.PartyID)
            param(2) = New SqlParameter("@PO_GRNID", ObjPurchase.PO_GRNID)
            param(3) = New SqlParameter("@PurchaseDate", ObjPurchase.PurchaseDate)
            param(4) = New SqlParameter("@PromoterID", ObjPurchase.PromoterID)
            param(5) = New SqlParameter("@SiteID", ObjPurchase.Site)
            param(6) = New SqlParameter("@Amount", ObjPurchase.Amount)
            param(7) = New SqlParameter("@OtherCharges", ObjPurchase.dblOtherCharges)
            param(8) = New SqlParameter("@TotalTaxes", ObjPurchase.TotalTax)
            param(9) = New SqlParameter("@Discount", ObjPurchase.Discount)
            param(10) = New SqlParameter("@GrossAmount", ObjPurchase.GrossAmt)
            param(11) = New SqlParameter("@ShipingAddress", ObjPurchase.ShippingAdress)
            param(12) = New SqlParameter("@Status", "1") 'ACTIVE
            param(13) = New SqlParameter("@CU", CCommon.GetUserID)
            param(14) = New SqlParameter("@CD", Date.Now.Date())
            param(15) = New SqlParameter("@LU", CCommon.GetUserID)
            param(16) = New SqlParameter("@LD", Date.Now.Date())
            param(17) = New SqlParameter("@FinYrId", CCommon.GetFinYrID)
            param(18) = New SqlParameter("@PurchaseType", ObjPurchase.PurchaseType)
            param(19) = New SqlParameter("@PriceListID", ObjPurchase.priceListID)
            param(20) = New SqlParameter("@SupplierDC", ObjPurchase.strSupplierDCNumber)
            param(21) = New SqlParameter("@Billed", 0)

            If dbConnection.ExecuteNonQuery(strSql, param) > 0 Then

                Return dbConnection.ExecuteScalar("SELECT TOP 1 PurchaseID FROM tPurchase ORDER BY PurchaseID DESC")
            Else
                Return 0
            End If


        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "SavePurchase")
            Return 0
        End Try

    End Function
    Public Shared Function UpdatePurchase(ByRef ObjPurchase As CPurchase, ByRef dbConnection As dbAccess, ByVal intVoucherID As Integer) As Integer

        Dim strSql As String = ""
        Dim param(13) As SqlParameter
        Try
            If ObjPurchase.PurchaseType <> DGeneral.DocTypes.PURCHASE_INVOICE_RETURN Then
                strSql = "UPDATE tPurchase SET PartyCode=@PartyCode,PromoterID=@PromoterID,SiteID=@SiteID,Amount=@Amount,OtherCharges=@OtherCharges,TotalTaxes=@TotalTaxes,Discount=@Discount,GrossAmount=@GrossAmount,ShipingAddress=@ShipingAddress,LU=@LU,LD=@LD,PriceListID=@PriceListID,PurchaseType=@PurchaseType WHERE PurchaseID=@PurchaseID"

                param(0) = New SqlParameter("@PartyCode", ObjPurchase.PartyID)
                param(1) = New SqlParameter("@PromoterID", ObjPurchase.PromoterID)
                param(2) = New SqlParameter("@SiteID", ObjPurchase.Site)
                param(3) = New SqlParameter("@Amount", ObjPurchase.Amount)
                param(4) = New SqlParameter("@OtherCharges", ObjPurchase.dblOtherCharges)
                param(5) = New SqlParameter("@TotalTaxes", ObjPurchase.TotalTax)
                param(6) = New SqlParameter("@Discount", ObjPurchase.Discount)
                param(7) = New SqlParameter("@GrossAmount", ObjPurchase.GrossAmt)
                param(8) = New SqlParameter("@ShipingAddress", ObjPurchase.ShippingAdress)

                param(9) = New SqlParameter("@LU", CCommon.GetUserID)
                param(10) = New SqlParameter("@LD", Date.Now.Date())
                param(11) = New SqlParameter("@PriceListID", ObjPurchase.priceListID)
                param(12) = New SqlParameter("@PurchaseID", intVoucherID)
                param(13) = New SqlParameter("@PurchaseType", IIf(ObjPurchase.blnConvertToInvoice, System.Convert.ToInt16(DGeneral.DocTypes.PURCHASE_INVOICE), ObjPurchase.PurchaseType))            '
                Return dbConnection.ExecuteNonQuery(strSql, param)
            Else
                Return dbConnection.ExecuteNonQuery("UPDATE tPurchase SET SupplierDC=" & ObjPurchase.strSupplierDCNumber)
            End If


        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "SavePurchase")
            Return 0
        End Try

    End Function

    Public Function UpdatePurchaseItemDetails(ByVal PurchaseID As Integer, ByRef dbConnection As dbAccess) As Integer

        Dim dtDItem As DataTable = GetPurchaseBillItems(PurchaseID)
        Dim objNgItem As CNGItem = New CNGItem
        Dim i As Integer
        Try
            For Each PurchaseItem As DataRow In dtDItem.Rows

                If objLocPurchase.PurchaseType <> DGeneral.DocTypes.PURCHASE_ORDER Then
                    objNgItem.isTinTin = False 'PurchaseItem.Item("IsTinTin")
                    objNgItem.Quantity = PurchaseItem.Item("Qty")
                    objNgItem.intPackUpdateDtl_PK = PurchaseItem.Item("PackUpdateIDPK")
                    If DGeneral.SubstractNGItemStock(objNgItem, dbConnection) = 0 Then
                        Return 0
                    End If
                End If
                i += 1
            Next
            Return i
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "SavePurchaseItemDetail", "")
            Return 0
        End Try
    End Function

    Public Function SavePurchaseItemDetail(ByVal PurchaseID As Integer, ByRef objPurchaseItem() As CNGItem, ByRef dbConnection As dbAccess) As Integer
        Dim strSql As String = String.Empty
        Dim param(14) As SqlParameter

        Dim intRecordsUpdate As Integer
        Dim i As Integer
        Try
            For Each Item As CNGItem In objPurchaseItem
                strSql = " INSERT INTO tPurchaseDet(PurchaseID,ItemID,CLRcode,PackUpdateID,PackSizeID,PurchasePrice,Qty,Amount,DISC,DISC_AMT,TAXID,TAXAMT,SchemeDiscount,FinYrId,BILL_ID)" & _
                         " VALUES(@PurchaseID,@ItemID,@CLRcode,@PackUpdateID,@PackSizeID,@PurchasePrice,@Qty,@Amount,@DISC,@DISC_AMT,@TAXID,@TAXAMT,@SchemeDiscount,@FinYrId,@BILL_ID)"

                param(0) = New SqlParameter("@PurchaseID", PurchaseID)
                param(1) = New SqlParameter("@ItemID", Item.ItemID)
                param(2) = New SqlParameter("@CLRcode", Item.ColorCode)
                param(3) = New SqlParameter("@PackUpdateID", Item.intPackUpdateDtl_PK)
                param(4) = New SqlParameter("@PackSizeID", Item.intPackSizeID)
                param(5) = New SqlParameter("@PurchasePrice", Item.dblRate)
                If objLocPurchase.PurchaseType = DGeneral.DocTypes.PURCHASE_GRN Then
                    param(6) = New SqlParameter("@Qty", Item.intReceivedQuantity)
                ElseIf objLocPurchase.PurchaseType = DGeneral.DocTypes.PURCHASE_INVOICE_RETURN Then
                    param(6) = New SqlParameter("@Qty", Item.intReceivedQuantity)
                Else 'If objLocPurchase.PurchaseType = DGeneral.DocTypes.PURCHASE_ORDER Then
                    param(6) = New SqlParameter("@Qty", Item.Quantity)
                End If


                param(7) = New SqlParameter("@Amount", Item.Amount)
                param(8) = New SqlParameter("@DISC", Item.strDisc)
                param(9) = New SqlParameter("@DISC_AMT", Item.dblDISCAmt)
                param(10) = New SqlParameter("@TAXID", Item.TaxID)
                param(11) = New SqlParameter("@TAXAMT", Item.TaxAmt)
                param(12) = New SqlParameter("@SchemeDiscount", Item.SchemeDiscount)
                param(13) = New SqlParameter("@FinYrId", CCommon.GetFinYrID)
                param(14) = New SqlParameter("@BILL_ID", Item.iBillID)
                
                i = dbConnection.ExecuteNonQuery(strSql, param)
                If i = 0 Then
                    Return i
                End If

                If objLocPurchase.PurchaseType = DGeneral.DocTypes.PURCHASE_GRN Then

                    If DGeneral.Add_GRN_ItemStock(Item, dbConnection) = 0 Then
                        Return 0
                    End If
                    If (dbConnection.ExecuteNonQuery("UPDATE tPurchaseDet SET ReceivedQty = ReceivedQty+ " & Item.intReceivedQuantity & " WHERE PurchaseID=" & Item.iBillID & " AND ItemID=" & Item.ItemID & " AND ClrCode=" & Item.ColorCode & " AND PackSizeID=" & Item.intPackSizeID) = 0) Then
                        Return 0
                    End If
                ElseIf objLocPurchase.PurchaseType = DGeneral.DocTypes.PURCHASE_INVOICE_RETURN Then

                    If DGeneral.SubstractNGItemStock(Item, dbConnection) = 0 Then
                        Return 0
                    End If
                    If (dbConnection.ExecuteNonQuery("UPDATE tPurchaseDet SET RetQty = RetQty + " & Item.intReceivedQuantity & " WHERE PurchaseID=" & Item.iBillID & " AND ItemID=" & Item.ItemID & " AND ClrCode=" & Item.ColorCode & " AND PackUpdateID=" & Item.intPackUpdateDtl_PK) = 0) Then
                        Return 0
                    End If
                End If
                intRecordsUpdate += i
            Next
            Return i
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "SavePurchaseItemDetail", strSql)
            Return 0
        End Try
    End Function
   

    Private Function SaveOtherCharges(ByRef dtbOtherCharges As DataTable, ByRef dbConnection As dbAccess) As Integer
        Dim strSql As String = "DELETE FROM tPurchaseOtherCharges WHERE PurchaseID=" + intPurchaseID.ToString()
        If dbConnection.ExecuteNonQuery(strSql) = 0 Then

        End If
        For Each drCharge As DataRow In dtbOtherCharges.Rows

            strSql = "INSERT INTO tPurchaseOtherCharges(PurchaseID,OtherChargeID,Value,Amount) VALUES(" & intPurchaseID.ToString & "," & drCharge.Item(0).ToString & ",'" & drCharge.Item(2).ToString & "'," & drCharge.Item(3).ToString & ") "
            If dbConnection.ExecuteNonQuery(strSql) = 0 Then
                Return 0
            End If
        Next
        Return 1
    End Function


    Public Shared Function SavePurchaseTaxDetails(ByVal BillID As Long, ByVal ItemID As Long, ByVal CLRcode As String, ByVal Amount As Double, ByRef dbConnection As dbAccess) As Integer
        Dim strSql As String = ""
        Dim param(8) As SqlParameter
        Dim intResult As Integer
        Try
            Dim dtDItemTaxes As New DataTable ' = dtDItemTaxes(
            dtDItemTaxes = DTax.GetItemTaxes(ItemID, CLRcode, Amount)
            If IsNothing(dtDItemTaxes) Then Return 1
            For Each Tax As DataRow In dtDItemTaxes.Rows

                strSql = " INSERT INTO tBillItemTax(BillItemSeqid,ItemId,BillID,BillType,CLRcode, " & _
                                     " FormulaId,TaxID,TaxPercent,TaxAmount) " & _
                                     " VALUES (@BillItemSeq,@ItemID,@BillID,@BillType,@CLRcode,@FormulaId,@TaxID,@TaxPerc,@TaxAmount)"

                param(0) = New SqlParameter("@BillItemSeq", DGeneral.GetSeqNum("BillItemTax", dbConnection, True))
                param(1) = New SqlParameter("@ItemID", ItemID)
                param(2) = New SqlParameter("@BillID", BillID)
                param(3) = New SqlParameter("@BillType", "S")
                param(4) = New SqlParameter("@CLRcode", CLRcode)
                param(5) = New SqlParameter("@FormulaID", "")
                param(6) = New SqlParameter("@TaxID", Tax("ID"))
                param(7) = New SqlParameter("@TaxPerc", Tax("Val"))
                param(8) = New SqlParameter("@TaxAmount", Tax("Amount"))
                intResult = intResult + dbConnection.ExecuteNonQuery(strSql, param)
            Next

            Return intResult

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "SavePurchaseTaxDetails", strSql)
            Return 0
        End Try
    End Function

    Public Shared Function SavePurchaseReturnItems(ByVal ReturnID As Integer, ByRef Items() As CReturnItem, ByRef dbConnection As dbAccess) As Integer

        Dim strSql As String
        Dim param(7) As SqlParameter
        Dim i As Integer

        Try

            For Each Item As CReturnItem In Items
                If Not IsNothing(Item) Then
                    strSql = " INSERT INTO tPurchaseReturnDet(PurchaseReturnID,ItemID,CLRcode,Qty,ReturnPrice,Amount,Reason,FinYrId)" & _
                                              " VALUES(@PurchaseReturnID,@ItemID,@CLRcode,@Qty,@ReturnPrice,@Amount,@Reason,@FinYrId)"

                    param(0) = New SqlParameter("@PurchaseReturnID", ReturnID)
                    param(1) = New SqlParameter("@ItemID", Item.ItemID)
                    param(2) = New SqlParameter("@CLRcode", Item.CLRcode)
                    param(3) = New SqlParameter("@Qty", Item.Quantity)
                    param(4) = New SqlParameter("@ReturnPrice", Item.Rate)
                    param(5) = New SqlParameter("@Amount", Item.Amount)
                    'param(6) = New SqlParameter("@Reason", Item.Reason)
                    'Modified By:-(2008/01/16)
                    param(6) = New SqlParameter("@Reason", IIf(IsNothing(Item.Reason), "", Item.Reason))
                    param(7) = New SqlParameter("@FinYrId", CCommon.GetFinYrID)

                    i = i + dbConnection.ExecuteNonQuery(strSql, param)
                End If
            Next
            Return i

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "SavePurchaseReturnItems")
            Return 0
        End Try
    End Function

#Region "GETDETAILS"
  
#Region "PO"
    Public Shared Function GetPurchaseBill(ByVal intSearchID As Int64) As DataTable
        Dim strSql As String = ""
        Try
            strSql = "SELECT TOP 1 S.SeqPurchaseID,S.PurchaseDate,S.PromoterID,ISNULL(R.SupplierName,'') AS PromoterName, ISNULL(R.SupplierCode,'') AS PromoterCode ,S.SiteID,S.PriceListID,S.Amount,S.OtherCharges,S.ShipingAddress,ISNULL(T.SiteName,'') AS SiteName,ISNULL(T.SiteCode,'') AS SiteCode ,S.SupplierDC,S.GrossAmount FROM tPurchase S  LEFT OUTER JOIN Supplier R ON (S.PromoterID= R.ID) LEFT OUTER JOIN [Site] T ON(S.SiteID=T.ID) WHERE S.PurchaseID=" & intSearchID.ToString()

            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "GetPurchaseBill", strSql)
            Return Nothing
        End Try
    End Function
    Public Shared Function GetPurchaseBill(ByVal strSearchID As String) As DataTable
        Dim strSql As String = ""
        Try
            strSql = "SELECT TOP 1 S.SeqPurchaseID,S.PurchaseDate,S.PromoterID,ISNULL(R.SupplierName,'') AS PromoterName, ISNULL(R.SupplierCode,'') AS PromoterCode ,S.SiteID,S.PriceListID,S.Amount,S.OtherCharges,S.ShipingAddress,ISNULL(T.SiteName,'') AS SiteName,ISNULL(T.SiteCode,'') AS SiteCode ,S.SupplierDC FROM tPurchase S  LEFT OUTER JOIN Supplier R ON (S.PromoterID= R.ID) LEFT OUTER JOIN [Site] T ON(S.SiteID=T.ID) WHERE S.PurchaseID in (" & strSearchID & ")"

            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "GetPurchaseBill", strSql)
            Return Nothing
        End Try
    End Function
    Public Shared Function GetPOBillItems(ByVal intSearchID As Int64) As DataTable
        Dim strSql As String = ""
        Try
            strSql = "SELECT S.ItemID,P.ProductName,P.ProductCode,S.CLRcode AS CLRID,C.ColorCode,C.ColorDescription, S.PackUpdateID AS PackUpdateIDPK,0 AS GodownId,ISNULL(S.PackSizeID,0) AS PackSizeID,0 AS PackingID,S.PurchasePrice,S.Qty,S.Amount,S.DISC,S.DISC_AMT,S.TAXID,S.TAXAMT, S.SchemeDiscount,'' AS GodownName ,ISNULL(PK.packSize + ' ' + PK.PackingDescription,'') AS PCK FROM tPurchaseDet S INNER JOIN Product p ON(S.ItemID=P.ID) INNER JOIN COLOR C ON (S.CLRCODE=C.ID) LEFT OUTER JOIN PackingSize PK ON (S.PackSizeID=PK.ID) WHERE S.PurchaseID=" & intSearchID.ToString()

            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "GetPurchaseBillItems", strSql)
            Return Nothing
        End Try
    End Function
    Public Shared Function GetPurchaseBillItems(ByVal intSearchID As Int64) As DataTable
        Dim strSql As String = ""
        Try
            strSql = "SELECT S.ItemID,P.ProductName,P.ProductCode,S.CLRcode AS CLRID,C.ColorCode,C.ColorDescription, S.PackUpdateID AS PackUpdateIDPK,ISNULL(D.GodownId,0) AS GodownId,ISNULL(D.PackingID,0) AS PackingID,ISNULL(D.PackSizeID,0) AS PackSizeID,S.PurchasePrice,S.Qty,S.Amount,S.DISC,S.DISC_AMT,S.TAXID,S.TAXAMT, S.SchemeDiscount,ISNULL(G.GodownName,'') AS GodownName ,ISNULL(PK.packSize + ' ' + PK.PackingDescription,'') AS PCK FROM tPurchaseDet S INNER JOIN Product p ON(S.ItemID=P.ID) INNER JOIN COLOR C ON (S.CLRCODE=C.ID)LEFT OUTER  JOIN PackingUpdateDetail D ON(D.ID=S.PAckUpdateID)LEFT OUTER JOIN PackingSize PK ON (D.PackSizeID=PK.ID) LEFT OUTER JOIN Godown G ON(D.GodownID=G.ID) WHERE S.PurchaseID=" & intSearchID.ToString()
            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "GetPurchaseBillItems", strSql)
            Return Nothing
        End Try
    End Function
    'End Function
#End Region

#Region "GRN"
    Public Shared Function GetPurBillItems4GRN(ByVal strSearchIDs As String) As DataTable
        Dim strSql As String = ""
        Try
            strSql = "SELECT PurchaseID,S.ItemID,P.ProductName,P.ProductCode,S.CLRcode AS CLRID,C.ColorCode,C.ColorDescription, S.PackUpdateID AS PackUpdateIDPK,0 AS GodownId,ISNULL(S.PackSizeID,0) AS PackSizeID,0 AS PackingID,S.PurchasePrice,S.Qty,S.ReceivedQty,S.Amount,S.DISC,S.DISC_AMT,S.TAXID,S.TAXAMT, S.SchemeDiscount,'' AS GodownName ,ISNULL(PK.packSize + ' ' + PK.PackingDescription,'') AS PCK FROM tPurchaseDet S INNER JOIN Product p ON(S.ItemID=P.ID) INNER JOIN COLOR C ON (S.CLRCODE=C.ID) LEFT OUTER JOIN PackingSize PK ON (S.PackSizeID=PK.ID) WHERE S.PurchaseID  IN ( " & strSearchIDs & " )  AND Qty > ReceivedQty"

            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "GetPurchaseBillItems", strSql)
            Return Nothing
        End Try
    End Function
    Public Shared Function GetPurBillItems4GRN_EDIT(ByVal strSearchIDs As String) As DataTable
        Dim strSql As String = ""
        Try
            strSql = "SELECT S2.PurchaseID,S.ItemID,P.ProductName,P.ProductCode,S.CLRcode AS CLRID,C.ColorCode,C.ColorDescription, S.PackUpdateID AS PackUpdateIDPK,D.GodownId,D.PackSizeID,ISNULL(D.PackingID,0) AS PackingID,S.PurchasePrice,S2.Qty,(S2.ReceivedQty-S.Qty) AS ReceivedQty,S.Qty AS DISC, S.Amount,S.DISC_AMT,S.TAXID,S.TAXAMT, S.SchemeDiscount,G.GodownName AS GodownName ,ISNULL(PK.packSize + ' ' + PK.PackingDescription,'') AS PCK FROM tPurchaseDet S INNER JOIN tPurchaseDet S2 ON(S.Bill_ID=S2.PurchaseID)INNER JOIN Product p ON(S.ItemID=P.ID) INNER JOIN COLOR C ON (S.CLRCODE=C.ID)INNER JOIN PackingUpdateDetail D ON(D.ID=S.PAckUpdateID)INNER JOIN PackingSize PK ON (D.PackSizeID=PK.ID) INNER JOIN Godown G ON(D.GodownID=G.ID) WHERE S.PurchaseID = " & strSearchIDs

            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "GetPurchaseBillItems", strSql)
            Return Nothing
        End Try
    End Function
#End Region

#Region "PURCHASE INVOICE"
    Public Shared Function GetPurBillItems4_INVOICE(ByVal strSearchIDs As String) As DataTable
        Dim strSql As String = ""
        Try
            strSql = " SELECT PurchaseID,S.ItemID,P.ProductName,P.ProductCode,S.CLRcode AS CLRID,C.ColorCode,C.ColorDescription, S.PackUpdateID AS PackUpdateIDPK,D.GodownId,ISNULL(S.PackSizeID,0) AS PackSizeID,D.PackingID,ISNULL(DPLD.PricePerPack,0) AS  PurchasePrice,S.Qty,S.ReceivedQty,(S.Qty * ISNULL(DPLD.PricePerPack,0) ) AS Amount,S.DISC,S.DISC_AMT,S.TAXID,S.TAXAMT, S.SchemeDiscount,G.GodownName AS GodownName ,ISNULL(PK.packSize + ' ' + PK.PackingDescription,'') AS PCK	FROM tPurchaseDet S  INNER JOIN Product p ON(S.ItemID=P.ID) INNER JOIN COLOR C ON (S.CLRCODE=C.ID) INNER JOIN PackingUpdateDetail D ON(D.ID=S.PAckUpdateID)INNER JOIN PackingSize PK ON (D.PackSizeID=PK.ID) INNER JOIN Godown G ON(D.GodownID=G.ID) LEFT OUTER JOIN DealersPriceList DPL ON (DPL.GroupId =C.GroupId AND DPL.ProductId =P.ID)LEFT OUTER JOIN DealersPriceListDetail DPLD ON (DPL.Id=DPLD.DealersPriceListId AND DPLD.PackingId =PK.ID) WHERE S.PurchaseID IN ( " & strSearchIDs & " )"

            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "GetPurchaseBillItems", strSql)
            Return Nothing
        End Try
    End Function
#End Region
#Region "PURCHASE RETURN"
    Public Shared Function GetPurBillItems4_RETURN(ByVal strSearchIDs As String) As DataTable
        Dim strSql As String = ""
        Try
            strSql = "SELECT S.PurchaseID,S.ItemID,P.ProductName,P.ProductCode,S.CLRcode AS CLRID,C.ColorCode,C.ColorDescription, S.PackUpdateID  AS PackUpdateIDPK ,D.GodownId,ISNULL(D.PackSizeID,0) AS PackSizeID,D.PackingID,S.PurchasePrice,S.Qty,S.RetQty AS ReceivedQty,  '' AS  DISC, S.Amount,S.DISC_AMT,S.TAXID,S.TAXAMT, S.SchemeDiscount,G.GodownName AS GodownName ,ISNULL(PK.packSize + ' ' + PK.PackingDescription,'') AS PCK FROM tPurchaseDet S INNER JOIN Product p ON(S.ItemID=P.ID) INNER JOIN COLOR C ON (S.CLRCODE=C.ID) INNER JOIN PackingUpdateDetail D ON(D.ID=S.PAckUpdateID)INNER JOIN PackingSize PK ON (D.PackSizeID=PK.ID) INNER JOIN Godown G ON(D.GodownID=G.ID) WHERE S.PurchaseID = " & strSearchIDs

            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "GetPurchaseBillItems", strSql)
            Return Nothing
        End Try
    End Function
    Public Shared Function GetPurBillItems4_RETURN_EDIT(ByVal strSearchIDs As String) As DataTable
        Dim strSql As String = ""
        Try
            strSql = "SELECT S2.PurchaseID,S.ItemID,P.ProductName,P.ProductCode,S.CLRcode AS CLRID,C.ColorCode,C.ColorDescription, S.PackUpdateID AS PackUpdateIDPK,D.GodownId,D.PackSizeID,ISNULL(D.PackingID,0) AS PackingID,S.PurchasePrice,S2.Qty,(S2.RetQty-S.Qty) AS ReceivedQty,S.Qty AS DISC, S.Amount,S.DISC_AMT,S.TAXID,S.TAXAMT, S.SchemeDiscount,G.GodownName AS GodownName ,ISNULL(PK.packSize + ' ' + PK.PackingDescription,'') AS PCK FROM tPurchaseDet S INNER JOIN tPurchaseDet S2 ON(S.Bill_ID=S2.PurchaseID AND S.PackUpdateID=S2.PackUpdateID)INNER JOIN Product p ON(S.ItemID=P.ID) INNER JOIN COLOR C ON (S.CLRCODE=C.ID)INNER JOIN PackingUpdateDetail D ON(D.ID=S.PAckUpdateID)INNER JOIN PackingSize PK ON (D.PackSizeID=PK.ID) INNER JOIN Godown G ON(D.GodownID=G.ID) WHERE S.PurchaseID = " & strSearchIDs

            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "GetPurchaseBillItems", strSql)
            Return Nothing
        End Try
    End Function
#End Region

    Public Shared Function GetPurchaseTaxes(ByVal intSearchID As Int64) As DataTable
        Dim strSql As String = ""
        Try
            strSql = "SELECT S.TaxID,S.TAX,S.TaxableAmt,S.TaxAmt, TaxAmt+ TaxableAmt AS TOTAL FROM tPurchaseTax S WHERE S.PurchaseID=" & intSearchID.ToString() & " UNION SELECT Id AS TAXID,TaxPercentage AS TAX,'0' AS TAXABLEAMT,'0' AS TAXAMT,'0' AS TOTAL FROM TAX WHERE IsDeleted=0 AND ID NOT IN(SELECT DISTINCT S.TaxID FROM  tPurchaseTax S WHERE S.PurchaseID=" & intSearchID.ToString() & ")"

            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "GetPurchaseTaxes", strSql)
            Return Nothing
        End Try
    End Function


    Public Shared Function GetPurchaseBillOtherCharges(ByVal intSearchID As Int64) As DataTable
        Dim strSql As String = ""
        Try
            strSql = "SELECT O.ID,O.Chargename,ISNULL(S.Value,0)AS Value,ISNULL(S.Amount,0) AS Amount,O.IsTaxable FROM tPurchaseOtherCharges S RIGHT OUTER JOIN OtherCharge O ON (S.OtherChargeID=O.ID) WHERE O.IsDeleted =0 AND O.AddToInvoice =1 AND S.PurchaseID=" & intSearchID.ToString()

            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "", strSql)
            Return Nothing
        End Try
    End Function


    Public Shared Function GetItemUnit(ByVal ItemID As Integer, ByVal CLRcode As String) As String
        Try
            Return DbHelper.ExecuteScalar("SELECT SellUnit FROM tStock WHERE ItemID=" & ItemID & " AND CLRcode='" & CLRcode & "'")
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "GetItemUnit")
            Return ""
        End Try
    End Function

    Public Shared Function GetItemPackUnit(ByVal ItemID As Integer, ByVal CLRcode As String) As DataTable
        Try
            Return DbHelper.ExecuteDataTable("SELECT SellPack,SellUnit,ExpDate,ConversionFactor FROM tStock WHERE ItemID=" & ItemID & " AND CLRcode='" & CLRcode & "'")
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "GetItemPackUnit")
            Return Nothing
        End Try
    End Function

#End Region '"GETDETAILS"
    Public Shared Function GetParty() As DataTable
        Return DbHelper.ExecuteDataTable("SELECT P.Id,P.PartyCode,P.PartyName FROM [NGSPaintsDB].[dbo].Party P WHERE P.IsDeleted=0")
    End Function
    Public Shared Function GetPromoter() As DataTable
        Return DbHelper.ExecuteDataTable("SELECT P.Id,P.PromoterCode,P.PromoterName FROM [NGSPaintsDB].[dbo].promoter P WHERE P.IsDeleted=0")
    End Function
    Public Shared Function GetColors() As DataTable
        Return DbHelper.ExecuteDataTable("SELECT [Id],[ColorCode],[ColorDescription] FROM [NGSPaintsDB].[dbo].[Color] WHERE [IsDeleted]=0")
    End Function

    Public Shared Function GetColors(ByVal intColumnIndex As Integer, ByRef RecordCount As Integer, ByVal intProductID As Integer) As SqlDataReader
        'Returns The ItemList used for Purchase Item List
        Dim StrSql As String = ""
        Try
            StrSql = "SELECT C.Id,C.ColorCode,C.ColorDescription FROM [NGSPaintsDB].[dbo].[Color] C INNER JOIN [NGSPaintsDB].[dbo].[ProductColor] P ON (P.COLORID  =C.ID) WHERE C.IsDeleted=0  AND P.ProductID=" & intProductID.ToString() & " ORDER BY "
            ''StrSql = "SELECT C.Id,C.ColorCode,C.ColorDescription,S.Quantity FROM [NGSPaintsDB].[dbo].[Color] C INNER JOIN [NGSPaintsDB].[dbo].[ProductColor] P ON (P.COLORID  =C.ID) INNER JOIN [NGSPaintsDB].[dbo].[tStock] S ON(P.ID= S.ItemID AND C.ID=S.CLRCode) WHERE C.IsDeleted=0  AND P.ProductID=" & intProductID.ToString() & " ORDER BY "
            Dim StrRecordCntSql As String = "SELECT Count(ID) FROM [NGSPaintsDB].[dbo].[Color]"


            'If intColumnIndex = 1 Then
            '    StrSql = StrSql & " ColorCode"
            'Else : StrSql = StrSql & " ColorDescription"
            'End If
            StrSql = StrSql & " ColorDescription"
            Dim param(0) As SqlParameter
            param(0) = New SqlParameter("@Orderby", IIf(intColumnIndex = 1, "Code", "Name"))

            RecordCount = DbHelper.ExecuteScalar(StrRecordCntSql)
            Return DbHelper.ExecuteDataReader(StrSql)

            'Call DbHelper.ExecuteDataReader(StrSql, Dtr)

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "GetItemList", StrSql)
            Return Nothing
        End Try
    End Function

    Public Shared Function GetGoDownList() As DataTable
        Return DbHelper.ExecuteDataTable("SELECT [Id],[GodownCode],[GodownName] FROM [NGSPaintsDB].[dbo].[Godown] WHERE IsDeleted=0")
    End Function

    'Public Shared Function GetSiteList() As DataTable
    '    Return DbHelper.ExecuteDataTable(" SELECT [Id],[SiteCode],[SiteName] FROM [NGSPaintsDB].[dbo].[Site] WHERE [IsDeleted]=0")
    'End Function
    Public Shared Function GetPriceList() As DataTable
        Return DbHelper.ExecuteDataTable("SELECT [ID],[PRICELISTCODE],[PRICELISTNAME] FROM [NGSPAINTSDB].[DBO].[PRICELIST] WHERE [ISDELETED]=0")
    End Function
    Public Shared Function GetPackList() As DataTable
        Return DbHelper.ExecuteDataTable("SELECT [Id] AS PACKID ,([PackingContain] + ' '+ [PackingCode]+ ' '+ [PackingDescription]) AS PACK  FROM [NGSPaintsDB].[dbo].[Packing] WHERE [IsDeleted]=0")

    End Function

    Public Shared Function GetOtherCharges() As DataTable
        Dim strSql As String = ""
        Try
            strSql = "SELECT Id,ChargeName,'0' AS Value,0.0 AS Amount,IsTaxable FROM OtherCharge WHERE IsDeleted =0 AND AddToInvoice =1"
            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "GetOtherCharges", strSql)
            Return Nothing
        End Try
    End Function
#Region "CommentedCode"

#End Region
End Class